package excel;

import com.microsoft.schemas.office.excel.CTClientData;
import com.microsoft.schemas.office.excel.STObjectType;
import com.microsoft.schemas.vml.CTShape;
import com.microsoft.schemas.vml.CTTextbox;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.IOUtils;
// import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.ddf.EscherOptRecord;
import org.apache.poi.hssf.model.InternalWorkbook;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.openxml4j.opc.*;
import org.apache.poi.ss.formula.EvaluationWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xddf.usermodel.text.XDDFTextBody;
import org.apache.poi.xssf.usermodel.*;
import org.apache.xmlbeans.XmlCursor;
import org.apache.xmlbeans.XmlInteger;
import org.apache.xmlbeans.XmlObject;
import org.apache.xmlbeans.XmlString;
import org.apache.xmlbeans.impl.values.TypeStoreUser;
import org.apache.xmlbeans.impl.values.XmlAnyTypeImpl;
import org.openxmlformats.schemas.drawingml.x2006.main.CTTextBody;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTControls;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTLegacyDrawing;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTWorksheetImpl;

import javax.xml.namespace.QName;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigInteger;
import java.net.URI;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 如何获取从Excel工作表中选择的复选框值 xlsx
 * https://www.52dianzi.com/category/article/37/788341.html
 */
public class ReadExcelXSSFControls {

    private final static Pattern P = Pattern.compile("\\[sid=0x000A.+?\\[0(\\d),");
    private final static Pattern P12 = Pattern.compile("\\[sid=0x0012.+?\\[0(\\d),");

    public static void main(String[] args) throws Exception {
        ReadExcelXSSFControls o = new ReadExcelXSSFControls("java-tools/src/main/java/excel/Excel.xlsx");
    }

    public ReadExcelXSSFControls(String pathFile) throws Exception {
        try (FileInputStream fileInputStream = new FileInputStream(pathFile);
             // XSSFWorkbook wb = (XSSFWorkbook) WorkbookFactory.create(fileInputStream);
             Workbook wb = WorkbookFactory.create(fileInputStream);
        ) {
            Sheet sheet = wb.getSheetAt(0);
            if (sheet instanceof HSSFSheet) {
                getControls2((HSSFSheet) sheet);
            } else {

                XSSFDrawing drawingPatriarch = ((XSSFSheet) sheet).getDrawingPatriarch(); //sheet.createDrawingPatriarch();
                List<XSSFShape> shapes = drawingPatriarch.getShapes();
                for(XSSFShape shape: shapes) {
                    String shapeName = shape.getShapeName();
                    System.out.println(shapeName);
                    if (shape instanceof XSSFTextBox) //普通文本框
                    {
                        XSSFTextBox textbox = (XSSFTextBox) shape;
                        // XSSFRichTextString
                        String str = textbox.getText();
                    }

                    Row row = sheet.getRow(0);
                    // dx1 4352
                    int columnWidth = sheet.getColumnWidth(0);// 单元格宽度
                    // dy1 516
                    short height = row.getHeight(); // 单元格高度 即行高

                    if(shape instanceof XSSFSimpleShape){ // 文本框 艺术字
                        XSSFSimpleShape s = (XSSFSimpleShape)shape;
                        XSSFAnchor anchor = s.getAnchor();
                        // http://51helpdoc.com/doc/poi/5.0.0/apidocs/org/apache/poi/xssf/usermodel/XSSFClientAnchor.html
                        long colOff = ((XSSFClientAnchor) anchor).getFrom().getColOff();
                        long rowOff = ((XSSFClientAnchor) anchor).getFrom().getRowOff();

                        // http://51helpdoc.com/doc/poi/5.0.0/apidocs/org/apache/poi/xssf/usermodel/XSSFSimpleShape.html
                        // STShapeType.Enum RECT = STShapeType.Enum.forString("rect"); 5 矩形 文本框 艺术字 ShapeTypes.ELLIPSE
                        // INT_ELLIPSE = 35; 椭圆
                        System.out.println(s.getShapeType()); // 获取形状类型
                        XDDFTextBody textBody = s.getTextBody();
                        CTTextBody xmlObject = textBody.getXmlObject();

                        List<XSSFTextParagraph> textParagraphs = s.getTextParagraphs();
                        if (CollectionUtils.isNotEmpty(textParagraphs)) {
                            for (XSSFTextParagraph xssfTextParagraph : textParagraphs) {
                                List<XSSFTextRun> textRuns = xssfTextParagraph.getTextRuns();
                                if (CollectionUtils.isNotEmpty(textRuns)) {
                                    System.out.println(textRuns.get(0).getText());
                                }
                            }
                        }
                    }

                }

                CTWorksheet ctWorksheet = ((XSSFSheet) sheet).getCTWorksheet();
                CTWorksheetImpl ctWorksheetImpl = (CTWorksheetImpl) ctWorksheet;

                List<Control> controls = getControls((XSSFSheet) sheet);
                controls.forEach(o -> System.out.println(o.toString()));
            }

            // 修改输出到新文件
            // FileOutputStream out = new FileOutputStream("Excel3.xlsx");
            FileOutputStream out = new FileOutputStream("Excel4.xls");
            wb.write(out);
            out.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private List<Control> getControls2(HSSFSheet sheet) throws Exception {
        HSSFWorkbook workbook = sheet.getWorkbook();
        EscherAggregate drawingEscherAggregate = sheet.getDrawingEscherAggregate();
        InternalWorkbook internalWorkbook = workbook.getInternalWorkbook();
        EvaluationWorkbook.ExternalSheet externalSheet = internalWorkbook.getExternalSheet(0);

        HSSFPatriarch drawingPatriarch = sheet.getDrawingPatriarch();
        List<HSSFShape> hSSFShapes = drawingPatriarch.getChildren();
        for (HSSFShape hSSFShape : hSSFShapes) {
            if (hSSFShape instanceof HSSFSimpleShape) {
                HSSFSimpleShape simpleShape = (HSSFSimpleShape) hSSFShape;
                int shapeType = simpleShape.getShapeType();
                String shapeName = simpleShape.getShapeName();
                System.out.println("shapeName:" + shapeName); // Check Box 116

                if (shapeName.startsWith("Check Box")) {
                    System.out.println("Check Box:" + simpleShape.getString());
                }
                try {
                    if ("拒绝".equals(simpleShape.getString().getString())) {
                        System.out.println();

                        Field _textObjectRecord = HSSFSimpleShape.class.getDeclaredField("_textObjectRecord");
                        _textObjectRecord.setAccessible(true);
                        TextObjectRecord textObjectRecord = (TextObjectRecord)_textObjectRecord.get(simpleShape);
                        String string = textObjectRecord.toString();
                        System.out.println();
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }

                HSSFAnchor anchor = simpleShape.getAnchor();
                EscherOptRecord optRecord = simpleShape.getOptRecord();

                // .textrun = 24 .textrun = 46
                // HSSFSimpleShape._textObjectRecord
                /** 未选中 拒绝
                 * SUBRECORD: org.apache.poi.hssf.record.SubRecord$UnknownSubRecord [sid=0x000A size=12 : [00, 00, 83, D3, 00, 00, 00, 00, 00, 00, 03, 00]]
                 * SUBRECORD: org.apache.poi.hssf.record.SubRecord$UnknownSubRecord [sid=0x0014 size=14 : [07, 00, 00, 00, 00, 00, 3A, 01, 00, 02, 00, 08, 00, 08]]
                 * SUBRECORD: org.apache.poi.hssf.record.SubRecord$UnknownSubRecord [sid=0x0012 size=8 : [00, 00, 00, 00, 00, 00, 03, 00]]
                 */
                /** 选中
                 * SUBRECORD: org.apache.poi.hssf.record.SubRecord$UnknownSubRecord [sid=0x000A size=12 : [01, 00, 83, D3, 00, 00, 00, 00, 00, 00, 03, 00]]
                 * SUBRECORD: org.apache.poi.hssf.record.SubRecord$UnknownSubRecord [sid=0x0014 size=14 : [07, 00, 00, 00, 00, 00, 3A, 01, 00, 02, 00, 08, 00, 08]]
                 * SUBRECORD: org.apache.poi.hssf.record.SubRecord$UnknownSubRecord [sid=0x0012 size=8 : [01, 00, 00, 00, 00, 00, 03, 00]]
                 */
                Field _objRecord = HSSFShape.class.getDeclaredField("_objRecord");
                _objRecord.setAccessible(true);
                ObjRecord objRecord = (ObjRecord)_objRecord.get(hSSFShape);
                short sid = objRecord.getSid();

                List<SubRecord> subRecords = objRecord.getSubRecords();
                for (SubRecord subRecord : subRecords) {
                    System.out.println(subRecord.getClass().getCanonicalName());
                    if (subRecord instanceof CommonObjectDataSubRecord) {
                        CommonObjectDataSubRecord dataSubRecord = (CommonObjectDataSubRecord) subRecord;
                        // if (dataSubRecord.getObjectType() == CommonObjectDataSubRecord.OBJECT_TYPE_CHECKBOX) {
                        //     System.out.println("ObjId: "
                        //             + dataSubRecord.getObjectId() + "\nDetails: "
                        //             + dataSubRecord.toString());
                        // }

                    }
                    if (!(subRecord instanceof CommonObjectDataSubRecord)) {
                        Matcher m = P.matcher(subRecord.toString());
                        if (m.find()) {
                            // org.apache.poi.hssf.record.SubRecord$UnknownSubRecord [sid=0x000A size=12 : [01, 00, 20, E6, 00, 00, 00, 00, 00, 00, 03, 00]]
                            System.out.println(subRecord.toString());

                            try {
                                // java 反射获取内部静态类 https://blog.51cto.com/u_16213343/7067111
                                Class outerClass = SubRecord.class;
                                Class innerClass = outerClass.getDeclaredClasses()[0];
                                // 使用内部静态类的Class对象操作类的属性和方法
                                Field field = innerClass.getDeclaredField("_data");
                                field.setAccessible(true);
                                byte[] _data = (byte[])field.get(subRecord);
                                System.out.println(_data);

                                _data[0] = 0;
                            } catch (Exception e) {
                                e.printStackTrace();
                            }

                            String checkBit = m.group(1);
                            if (checkBit.length() == 1) {
                                boolean checked = "1".equals(checkBit);
                                System.out.println("--------" + checked + "-----------" + checkBit);
                            }
                        }

                        Matcher m12 = P12.matcher(subRecord.toString());
                        if (m12.find()) {
                            try {
                                // java 反射获取内部静态类 https://blog.51cto.com/u_16213343/7067111
                                Class outerClass = SubRecord.class;
                                Class innerClass = outerClass.getDeclaredClasses()[0];
                                // 使用内部静态类的Class对象操作类的属性和方法
                                Field field = innerClass.getDeclaredField("_data");
                                field.setAccessible(true);
                                byte[] _data = (byte[])field.get(subRecord);

                                _data[0] = 0;
                            } catch (Exception e) {
                                e.printStackTrace();
                            }

                        }
                    }

                }

                System.out.println();
            }

            System.out.println();
        }

        return null;
    }

    private List<Control> getControls(XSSFSheet sheet) throws Exception {
        // xl\worksheets\_rels\sheet1.xml.rels 读取的是这个文件的内容 汇总 <Relationships
        // xl\ctrlProps\ctrlProp1.xml  <formControlPr

        List<POIXMLDocumentPart> relations = sheet.getRelations();
        List<POIXMLDocumentPart.RelationPart> relationParts = sheet.getRelationParts();
        for (POIXMLDocumentPart.RelationPart relationPart : relationParts) {
            PackageRelationship relationship = relationPart.getRelationship();
            POIXMLDocumentPart documentPart = relationPart.getDocumentPart();
            String relationshipType = relationship.getRelationshipType();

            // rid和控制文件对应
            String rid = relationship.getId();
            OPCPackage aPackage = relationship.getPackage();
            TargetMode targetMode = relationship.getTargetMode();
            URI targetURI = relationship.getTargetURI();
        }
        for (POIXMLDocumentPart relation : relations) {
            System.out.println("class: " + relation.getClass().getCanonicalName());
            if (relation instanceof XSSFDrawing) {
                System.out.println("instanceof XSSFDrawing");

                XSSFDrawing drawing = (XSSFDrawing) relation;

                List<XSSFShape> shapes = drawing.getShapes();
                for(XSSFShape shape: shapes){
                    if(shape instanceof XSSFSimpleShape){
                        XSSFSimpleShape s = (XSSFSimpleShape)shape;
                        List<XSSFTextParagraph> textParagraphs = s.getTextParagraphs();
                        if (CollectionUtils.isNotEmpty(textParagraphs)) {
                            for (XSSFTextParagraph xssfTextParagraph : textParagraphs) {
                                List<XSSFTextRun> textRuns = xssfTextParagraph.getTextRuns();
                                if (CollectionUtils.isNotEmpty(textRuns)) {
                                    System.out.println(textRuns.get(0).getText());
                                }
                            }
                        }
                    }
                }

            }
            if (relation instanceof XSSFVMLDrawing) {
                System.out.println("instanceof XSSFVMLDrawing");
            }

            // ControlProperties 类 此类仅在 Office2010 中可用。当对象序列化为 xml 时，其限定名称为 x:controlPr
            // https://learn.microsoft.com/zh-cn/dotnet/api/documentformat.openxml.spreadsheet.controlproperties
            // https://learn.microsoft.com/zh-cn/openspecs/office_standards/ms-xlsx/3d054a6d-4f94-4082-837a-f939fd8d4a45
            // Name: /xl/drawings/vmlDrawing1.vml - Content Type: application/vnd.openxmlformats-officedocument.vmlDrawing
            // Name: /xl/ctrlProps/ctrlProp1.xml - Content Type: application/vnd.ms-excel.controlproperties+xml
            System.out.println(relation.toString());

            if (relation.toString().indexOf("application/vnd.ms-excel.controlproperties+xml") > -1) {
                PackagePart packagePart = relation.getPackagePart();
                // org.apache.poi.openxml4j.opc.ZipPackagePart
                System.out.println(packagePart.getClass().getCanonicalName());

                ZipPackagePart zipPackagePart = (ZipPackagePart) packagePart;
                PackagePartName partName = zipPackagePart.getPartName();

                if (relation.toString().indexOf("/xl/ctrlProps/ctrlProp1.xml") > -1) {
                    try (InputStream inputStream = packagePart.getInputStream();) {
                        String result = IOUtils.toString(inputStream, StandardCharsets.UTF_8);
                        System.out.println(result);

                        if (result.indexOf("checked=\"Checked\"") < 0) {
                            String replace = result.replace("objectType=\"CheckBox\"", "objectType=\"CheckBox\" checked=\"Checked\" ");
                            //Method not implemented !
                            // zipPackagePart.load(IOUtils.toInputStream(replace));

                            // try (OutputStream outputStream = packagePart.getOutputStream();) {
                            //     IOUtils.write(replace, outputStream);
                            // } catch (Exception e) {
                            //     e.printStackTrace();
                            // }

                        }

                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }

            }

        }

        CTWorksheet ctWorksheet = sheet.getCTWorksheet();
        CTWorksheetImpl ctWorksheetImpl = (CTWorksheetImpl) ctWorksheet;
        // private static final QName CONTROLS$68 = new QName("http://schemas.openxmlformats.org/spreadsheetml/2006/main", "controls");
        // CTControls var2 = (CTControls)this.get_store().find_element_user(CONTROLS$68, 0);
        // org.openxmlformats.schemas.spreadsheetml.x2006.main.CTControls ctControls = ctWorksheet.getControls();

        // xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        /**
         * xl\worksheets\sheet1.xml
         *     <drawing r:id="rId1"/>
         *     <legacyDrawing r:id="rId2"/>
         *     <mc:AlternateContent>
         *         <mc:Choice Requires="x14">
         *             <controls>
         *                 <mc:AlternateContent>
         *                     <mc:Choice Requires="x14">
         *                         <control shapeId="1025" name="Check Box 1" r:id="rId3">
         *                             <controlPr defaultSize="0">
         *                                 <anchor moveWithCells="1">
         */
        QName alternateContentQName = new QName("http://schemas.openxmlformats.org/markup-compatibility/2006", "AlternateContent");
        QName controlsQName = new QName("http://schemas.openxmlformats.org/spreadsheetml/2006/main", "controls", "main");
        // 只能筛选一级元素
        TypeStoreUser alternateElement = ctWorksheetImpl.get_store().find_element_user(alternateContentQName, 0);

        TypeStoreUser choice = null;
        if (alternateElement != null) {
            alternateElement.get_store().find_element_user(
                    new QName("http://schemas.openxmlformats.org/markup-compatibility/2006", "Choice"), 0);
        }
        if (choice != null) {
            // Cannot cast 'org.apache.xmlbeans.impl.values.XmlAnyTypeImpl' to 'org.openxmlformats.schemas.spreadsheetml.x2006.main.CTControls'
            TypeStoreUser controls = choice.get_store().find_element_user(controlsQName, 0);
        }
        // <xml-fragment xmlns:mc=...><mc:AlternateContent><mc:Choice Requires="x14"><main:control shapeId="1025"
        // </mc:Choice></mc:AlternateContent><mc:AlternateContent><mc:Choice Requires="x14"><main:control
        // </main:controlPr></main:control></mc:Choice></mc:AlternateContent></xml-fragment>
        // ?只匹配到一个
        // String replace = ((XmlAnyTypeImpl) controls).xmlText().replaceAll("(<xml-fragment.*?>)(.*?)(<main:control)", "$1$3");
        // replace = replace.replaceAll("(</main:control>)(.*?)(<main:control)", "$1$3");
        // // 最后一个</main:control>到结尾
        // replace = replace.replaceAll("(.*</main:control>)(.*?)(</xml-fragment>)", "$1$3");
        //
        // CTControls ctControls = CTControls.Factory.parse(replace);
        // long shapeId1 = ctControls.getControlList().get(0).getShapeId();
        // String rid1 = ctControls.getControlList().get(0).getId();


        // 表单控件与rid对应
        List<Map<String, Object>> control2RidList = new ArrayList<>();
        if (alternateElement != null) {
            System.out.println(alternateElement.getClass().getCanonicalName());
            // 手动解析xml
            XmlCursor xmlCursor = alternateElement.get_store().new_cursor();

            QName controlQName = new QName("http://schemas.openxmlformats.org/spreadsheetml/2006/main", "control", "main");
            QName ridQName = new QName("http://schemas.openxmlformats.org/officeDocument/2006/relationships", "id", "r");

            while (xmlCursor.hasNextToken()) {
                XmlCursor.TokenType tokenType = xmlCursor.toNextToken();
                /*<control shapeId="1025" name="Check Box 1" r:id="rId3">
                            <controlPr defaultSize="0">*/
                // System.out.println("xmlCursor.getName(): " + xmlCursor.getName());
                if (tokenType.isStart()) {
                    if (controlQName.equals(xmlCursor.getName())) {
                        Map<String, Object> map = new HashMap<>();
                        control2RidList.add(map);

                        // 解析属性值
                        // <xml-fragment shapeId="1025" name="Check Box 1" r:id="rId3"
                        while (xmlCursor.hasNextToken()) {
                            tokenType = xmlCursor.toNextToken();
                            if (tokenType.isAttr()) {
                                QName name = xmlCursor.getName();
                                String namespaceURI = name.getNamespaceURI();
                                String localPart = name.getLocalPart();
                                String prefix = name.getPrefix();
                                if (new QName("shapeId").equals(xmlCursor.getName())) {
                                    System.out.println("shapeId: " + xmlCursor.getTextValue());
                                    map.put("shapeId", xmlCursor.getTextValue());
                                }
                                if (new QName("name").equals(xmlCursor.getName())) {
                                    System.out.println("name: " + xmlCursor.getTextValue());
                                    map.put("name", xmlCursor.getTextValue());
                                }
                                // {http://schemas.openxmlformats.org/officeDocument/2006/relationships}id
                                if (ridQName.equals(xmlCursor.getName())) {
                                    System.out.println("rid: " + xmlCursor.getTextValue());
                                    map.put("rid", xmlCursor.getTextValue());
                                }
                            } else {
                                break;
                            }
                        }

                    }
                }
            }

        }

        /*TypeStoreUser element_user2 = ctWorksheetImpl.get_store().find_element_user(controlQName, 0);

        Node domNode = ctWorksheetImpl.getDomNode();
        NodeList childNodes = domNode.getChildNodes();
        Node item = childNodes.item(childNodes.getLength() - 1);
        for (int i = 0; i < childNodes.getLength(); i++) {
            Node item1 = childNodes.item(i);
            String nodeName = item1.getNodeName();
            short nodeType = item1.getNodeType();
        }*/

        // TypeStore store = alternateElement.get_store();
        // System.out.println(store.getClass().getCanonicalName()); // org.apache.xmlbeans.impl.store.Xobj.ElementXobj

        /**
         * xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
         *   <mc:Choice Requires="x14">
         *     <main:controls>
         */

        // 依然找不到
        // TypeStoreUser element_user3 = store.find_element_user(controlsQName, 1);
        // TypeStoreUser element_user4 = alternateElement.get_store().find_element_user(
        //         new QName("http://schemas.openxmlformats.org/spreadsheetml/2006/main", "controlPr", "main"), 0);


        // java: org.apache.xmlbeans.impl.store.Xobj在org.apache.xmlbeans.impl.store中不是公共的; 无法从外部程序包中对其进行访问
        // org.apache.xmlbeans.impl.store.Xobj store1 = (org.apache.xmlbeans.impl.store.Xobj) store;

        // List<TypeStoreUser> list = new ArrayList<>();
        // element_user.get_store().find_all_element_users(controlQName, list);
        // CTCustomSheetViews customSheetViews = ctWorksheet.getCustomSheetViews();

        CTLegacyDrawing legacyDrawing = ctWorksheet.getLegacyDrawing();
        CTControls controls1 = ctWorksheet.getControls();

        List<Control> controlList = new ArrayList<>();
        if (legacyDrawing != null) {
            String legacyDrawingId = legacyDrawing.getId();
            POIXMLDocumentPart part = sheet.getRelationById(legacyDrawingId);

            if (part instanceof XSSFVMLDrawing) {
                // relations一致
                // Name: /xl/drawings/vmlDrawing1.vml - Content Type: application/vnd.openxmlformats-officedocument.vmlDrawing
                XSSFVMLDrawing vml = (XSSFVMLDrawing) part;

                // xl\drawings\vmlDrawing1.vml 读取的是这个文件的内容 <v:shape
                Field _items = XSSFVMLDrawing.class.getDeclaredField("_items");
                _items.setAccessible(true);
                @SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
                List<XmlObject> items = (List<XmlObject>) _items.get(vml);

                for (XmlObject xo : items) {
                    // com.microsoft.schemas.vml.CTShape
                    if (xo instanceof CTShape) {
                        CTShape ctShape = (CTShape) xo;
                        // https://www.saoniuhuo.com/article/detail-465325.html
                        List<CTClientData> clientDataList = ctShape.getClientDataList();
                        String title = ctShape.getTitle();
                        BigInteger regroupid = ctShape.getRegroupid();
                        XmlInteger xregroupid = ctShape.xgetRegroupid();
                        // <v:shape id="_x0000_s1025" o:spt="201" type="#_x0000_t201"
                        String shapeId = ctShape.getId();
                        XmlString xshapeId = ctShape.xgetId();

                        if (CollectionUtils.isNotEmpty(clientDataList)) {
                            CTClientData clientData = clientDataList.get(0);
                            // 类型
                            STObjectType.Enum type = clientData.getObjectType();
                            if (STObjectType.CHECKBOX.equals(type) || STObjectType.RADIO.equals(type)) {
                                // 复选框 或者 单选框
                                String objectType = type.toString();

                                // java.lang.ClassNotFoundException: com.microsoft.schemas.office.excel.impl.CTClientDataImpl$1CheckedList
                                // List<BigInteger> checkedList = clientData.getCheckedList();
                                BigInteger[] checkedArray = clientData.getCheckedArray();
                                XmlInteger[] checkedArray2 = clientData.xgetCheckedArray();
                                // 1, 14, 4, 20, 2, 19, 5, 30
                                // 2, 58, 4, 18, 3, 63, 5, 28
                                // cldata.addNewAnchor().setStringValue(
                                //         "" + col1 + ", " + dx1 + ", " + row1 + ", " + dy1 + ", " + col2 + ", " + dx2 + ", " + row2 + ", " + dy2
                                // );
                                String[] anchorArray = clientData.getAnchorArray();

                                // System.out.println("------" + objectType + "-------" + (checkedArray.length > 0 ? clientData.getCheckedArray(0) : "null"));
                                // System.out.println("------" + objectType + "-------" + clientData.getAnchorArray(0));

                                String labelName = getLabelName(ctShape);
                                BigInteger checked = BigInteger.ZERO;
                                if (checkedArray != null && checkedArray.length > 0) {
                                    checked = clientData.getCheckedArray(0);
                                } else {
                                    // 复选框勾选
                                    // 单选框可以添加 但是页面只有第一个显示选中效果，后面无视
                                    // clientData.insertChecked(0, BigInteger.ONE);
                                }

                                if (STObjectType.CHECKBOX.equals(type) && "复选框 1".equals(labelName)) {
                                    // 复选框选中
                                    selectOrCancel(clientData, checkedArray, 1, control2RidList, sheet, shapeId, objectType);
                                }
                                if (STObjectType.CHECKBOX.equals(type) && "复选框 2".equals(labelName)) {
                                    // 复选框取消选中
                                    selectOrCancel(clientData, checkedArray, 0, control2RidList, sheet, shapeId, objectType);
                                }
                                if (STObjectType.RADIO.equals(type) && "选项按钮 3".equals(labelName)) {
                                    // 单选框取消选中
                                    selectOrCancel(clientData, checkedArray, 0, control2RidList, sheet, shapeId, objectType);
                                }
                                if (STObjectType.RADIO.equals(type) && "选项按钮 4".equals(labelName)) {
                                    // 单选框取消选中
                                    selectOrCancel(clientData, checkedArray, 0, control2RidList, sheet, shapeId, objectType);
                                }
                                if (STObjectType.RADIO.equals(type) && "选项按钮 5".equals(labelName)) {
                                    // 单选框选中
                                    selectOrCancel(clientData, checkedArray, 1, control2RidList, sheet, shapeId, objectType);
                                }

                                String anchor = clientData.getAnchorArray(0);
                                Control control = new Control(labelName, objectType, checked.toString(), anchor);
                                controlList.add(control);
                            }
                        }

                        System.out.println("================");
                    }


                }

            }


        }
        return controlList;
    }

    private static void selectOrCancel(CTClientData clientData, BigInteger[] checkedArray, int type,
                                       List<Map<String, Object>> control2RidList, XSSFSheet sheet,
                                       String shapeId, String objectType) {
        if (type == 0) {
            // 取消选中
            if (checkedArray != null && checkedArray.length > 0) {
                // 没有效果 文件里面状态确实改变了，但是页面效果没有改变
                // clientData.removeChecked(0);
                // clientData.addChecked(BigInteger.ZERO);
                clientData.setCheckedArray(0, BigInteger.ZERO);

                // 修改对应的控制文件 不会自动修改 excel根据这个文件显示选择效果
                replaceXmlFile(type, control2RidList, sheet, shapeId, objectType);
            }
        }

        /**
         * <v:shape>
         *     <v:textbox o:singleclick="f">复选框</v:textbox>
         *     <x:ClientData ObjectType="Checkbox">
         *         <x:Anchor>1, 1, 4, 3, 1, 86, 5, 13</x:Anchor>
         *         <x:Checked>1</x:Checked>
         *     </x:ClientData>
         * </v:shape>
         */
        // xl\drawings\vmlDrawing1.vml 修改的是这个文件里面的<x:Checked>1</x:Checked>
        /**
         * <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
         * <formControlPr xmlns="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" objectType="CheckBox" checked="Checked" noThreeD="1"/>
         */
        // xl\ctrlProps\ctrlProp1.xml 但是复选框的实际选中效果是由这个文件的 checked="Checked" 控制的，每个表单控件单独一个文件

        /**
         * <?xml version="1.0" encoding="UTF-8" standalone="no"?>
         * <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
         * <Relationship Id="rId1" Target="../drawings/drawing1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing"/>
         * <Relationship Id="rId2" Target="../drawings/vmlDrawing1.vml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing"/>
         * <Relationship Id="rId3" Target="../ctrlProps/ctrlProp1.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/ctrlProp"/>
         * <Relationship Id="rId4" Target="../ctrlProps/ctrlProp2.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/ctrlProp"/>
         * <Relationship Id="rId5" Target="../ctrlProps/ctrlProp3.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/ctrlProp"/>
         * <Relationship Id="rId6" Target="../ctrlProps/ctrlProp4.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/ctrlProp"/>
         * <Relationship Id="rId7" Target="../ctrlProps/ctrlProp5.xml" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/ctrlProp"/>
         * </Relationships>
         */
        // xl\worksheets\_rels\sheet1.xml.rels 在这个文件 汇总引入
        if (type == 1) {
            // 选中
            if (checkedArray == null || checkedArray.length == 0) {
                clientData.insertChecked(0, BigInteger.ONE);

                // 修改对应的控制文件 不会自动修改 excel根据这个文件显示选择效果
                replaceXmlFile(type, control2RidList, sheet, shapeId, objectType);
            }
        }
    }

    /**
     * 替换xml文件内容
     *
     * @param type            0-取消选中，1-选中
     * @param control2RidList
     * @param sheet
     * @param shapeId
     * @param objectType
     */
    private static void replaceXmlFile(int type, List<Map<String, Object>> control2RidList, XSSFSheet sheet, String shapeId, String objectType) {
        if (CollectionUtils.isEmpty(control2RidList)) {
            return;
        }
        // <v:shape id="_x0000_s1025"
        String shapeIdTmp = shapeId.replace("_x0000_s", "");
        Map<String, Object> control2Rid = control2RidList.stream()
                .filter(o -> o.get("shapeId").toString().equals(shapeIdTmp))
                .findFirst().get();

        if (control2Rid == null || control2Rid.get("rid") == null) {
            return;
        }

        String rid = control2Rid.get("rid").toString();
        List<POIXMLDocumentPart.RelationPart> relationParts = sheet.getRelationParts();
        for (POIXMLDocumentPart.RelationPart relationPart : relationParts) {
            PackageRelationship relationship = relationPart.getRelationship();
            POIXMLDocumentPart documentPart = relationPart.getDocumentPart();

            // rid和控制文件对应
            if (relationship.getId().equals(rid)) {
                if (documentPart.toString().indexOf("application/vnd.ms-excel.controlproperties+xml") > -1) {
                    PackagePart packagePart = documentPart.getPackagePart();

                    ZipPackagePart zipPackagePart = (ZipPackagePart) packagePart;
                    PackagePartName partName = zipPackagePart.getPartName();

                    try (InputStream inputStream = packagePart.getInputStream();) {
                        String result = IOUtils.toString(inputStream, StandardCharsets.UTF_8);
                        System.out.println(result);

                        if (type == 0) {
                            // 取消选中
                            if (result.indexOf("checked=\"Checked\"") > -1) {
                                String replace = result.replace("checked=\"Checked\"", "");

                                // 回写
                                try (OutputStream outputStream = packagePart.getOutputStream();) {
                                    IOUtils.write(replace, outputStream);
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }

                            }
                        } else if (type == 1) {
                            // 选中
                            if (result.indexOf("checked=\"Checked\"") < 0) {
                                // 匹配不到为什么？？？ 字符串大小写不一致
                                objectType = "Checkbox".equals(objectType) ? "CheckBox" : objectType;
                                String srcStr = "objectType=\"" + objectType + "\"";
                                String targetStr = srcStr + " checked=\"Checked\"";

                                String replace = result.replace(srcStr, targetStr);

                                // 回写
                                try (OutputStream outputStream = packagePart.getOutputStream();) {
                                    IOUtils.write(replace, outputStream);
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }

                            }
                        }

                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                }

                break;
            }

        }
    }

    public static String getLabelName(CTShape ctShape) {
        String labelName = null;
        CTTextbox[] textboxArray = ctShape.getTextboxArray();
        if (textboxArray != null && textboxArray.length > 0) {
            CTTextbox ctTextbox = textboxArray[0];
            // 设置进去的时候就不是纯文本
            labelName = StripHT(ctTextbox.xmlText());
            System.out.println(labelName);
        }
        return labelName;
    }


    /**
     * Java实现从Html文本中提取纯文本的方法
     * https://www.jb51.net/article/140548.htm
     */
    public static String StripHT(String strHtml) {
        // 剔出<html>的标签
        String txtcontent = strHtml.replaceAll("</?[^>]+>", "");
        // 去除字符串中的空格,回车,换行符,制表符
        // \s代表一个空格 ，\s*对应零个或多个空格
        txtcontent = txtcontent.replaceAll("[\\s*|\t|\r|\n]", "");
        return txtcontent;
    }

    private class Control {
        private String name; // 名称
        private String objectType; // 类型
        private String checked; // 是否勾选
        private String anchor; // 瞄点 逗号连接
        private int row1;
        private int col1;
        private int row2;
        private int col2;

        public Control(String name, String objectType, String checked, String anchor) {
            this.name = name;
            this.objectType = objectType;
            this.checked = checked;
            this.anchor = anchor;

            String[] split = anchor.trim().split(",");
            int[] ints = Arrays.stream(split).mapToInt(o -> Integer.parseInt(o.trim())).toArray();
            // " + col1 + ", " + dx1 + ", " + row1 + ", " + dy1 + ", " + col2 + ", " + dx2 + ", " + row2 + ", " + dy2

            // 详解 利用NPOI向Excel指定位置中加入图片(支持.xlsx和.xls) https://blog.csdn.net/u013986317/article/details/102502794/
            //设置图片坐标与大小
            //函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
            //坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第六列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
            //坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
            //坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
            //坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小
            // public XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)
            this.row1 = ints[2];
            this.col1 = ints[0];
            this.row2 = ints[6];
            this.col2 = ints[4];
        }

        public String getName() {
            return this.name;
        }

        public String getObjectType() {
            return this.objectType;
        }

        public String getChecked() {
            return this.checked;
        }

        public int getRow1() {
            return this.row1;
        }

        public int getCol1() {
            return this.col1;
        }

        public int getRow2() {
            return this.row2;
        }

        public int getCol2() {
            return this.col2;
        }

        public String toString() {
            return this.name + ":r1/c1:" + this.row1 + "/" + this.col1 + ":r2/c2:" + this.row2 + "/" + this.col2 + ":" + this.checked + ":anchor:" + this.anchor;
        }
    }

}

